﻿using System;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using Database.DTO;
using System.Collections.Generic;
using DAL;

namespace Database.DAL
{
    /// <summary>
    /// 数据库类，提供对代码生成表的基本增、删、改、查方法
    /// </summary>
    public class DatabaseHelper
    {
        public static bool Insert(AccountDTO model)
{
 string sql = "insert into `account` (`AID`,`Email`,`Password`,`Account`) values (@AID,@Email,@Password,@Account)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("Email",model.Email),GetParams("Password",model.Password),GetParams("Account",model.Account));
model.AID=GetLastInsertID();
return r == 1;
}
public static bool Delete(AccountDTO model)
{
 string sql = "delete from `account` where AID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.AID));
return r == 1;
}
public static bool Update(AccountDTO model)
{
string sql = "update account set `Email`=@Email,`Password`=@Password,`Account`=@Account where AID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("Email",model.Email),GetParams("Password",model.Password),GetParams("Account",model.Account),GetParams("id",model.AID));
return r == 1;
}
public static bool Insert(Auto_setDTO model)
{
 string sql = "insert into `auto_set` (`ID`,`AID`,`AutoType`,`Money`,`Card_ID`,`Category_ID`,`Remark`,`Last_Update_Time`) values (@ID,@AID,@AutoType,@Money,@Card_ID,@Category_ID,@Remark,@Last_Update_Time)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("AID",model.AID),GetParams("AutoType",model.AutoType),GetParams("Money",model.Money),GetParams("Card_ID",model.Card_ID),GetParams("Category_ID",model.Category_ID),GetParams("Remark",model.Remark),GetParams("Last_Update_Time",model.Last_Update_Time));
model.ID=GetLastInsertID();
return r == 1;
}
public static bool Delete(Auto_setDTO model)
{
 string sql = "delete from `auto_set` where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.ID));
return r == 1;
}
public static bool Update(Auto_setDTO model)
{
string sql = "update auto_set set `AID`=@AID,`AutoType`=@AutoType,`Money`=@Money,`Card_ID`=@Card_ID,`Category_ID`=@Category_ID,`Remark`=@Remark,`Last_Update_Time`=@Last_Update_Time where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("AID",model.AID),GetParams("AutoType",model.AutoType),GetParams("Money",model.Money),GetParams("Card_ID",model.Card_ID),GetParams("Category_ID",model.Category_ID),GetParams("Remark",model.Remark),GetParams("Last_Update_Time",model.Last_Update_Time),GetParams("id",model.ID));
return r == 1;
}
public static bool Insert(BankbookDTO model)
{
 string sql = "insert into `bankbook` (`ID`,`AID`,`Card_ID`,`Rate`,`Capital`,`Cycle`,`Total`,`Save_Time`) values (@ID,@AID,@Card_ID,@Rate,@Capital,@Cycle,@Total,@Save_Time)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("AID",model.AID),GetParams("Card_ID",model.Card_ID),GetParams("Rate",model.Rate),GetParams("Capital",model.Capital),GetParams("Cycle",model.Cycle),GetParams("Total",model.Total),GetParams("Save_Time",model.Save_Time));
model.ID=GetLastInsertID();
return r == 1;
}
public static bool Delete(BankbookDTO model)
{
 string sql = "delete from `bankbook` where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.ID));
return r == 1;
}
public static bool Update(BankbookDTO model)
{
string sql = "update bankbook set `AID`=@AID,`Card_ID`=@Card_ID,`Rate`=@Rate,`Capital`=@Capital,`Cycle`=@Cycle,`Total`=@Total,`Save_Time`=@Save_Time where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("AID",model.AID),GetParams("Card_ID",model.Card_ID),GetParams("Rate",model.Rate),GetParams("Capital",model.Capital),GetParams("Cycle",model.Cycle),GetParams("Total",model.Total),GetParams("Save_Time",model.Save_Time),GetParams("id",model.ID));
return r == 1;
}
public static bool Insert(CardDTO model)
{
 string sql = "insert into `card` (`CID`,`AID`,`Type`,`Title`,`Money`,`Description`,`CardNumber`,`OID`,`Credit_Line`,`Bill_Date`,`Due_Date`) values (@CID,@AID,@Type,@Title,@Money,@Description,@CardNumber,@OID,@Credit_Line,@Bill_Date,@Due_Date)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("AID",model.AID),GetParams("Type",model.Type),GetParams("Title",model.Title),GetParams("Money",model.Money),GetParams("Description",model.Description),GetParams("CardNumber",model.CardNumber),GetParams("OID",model.OID),GetParams("Credit_Line",model.Credit_Line),GetParams("Bill_Date",model.Bill_Date),GetParams("Due_Date",model.Due_Date));
model.CID=GetLastInsertID();
return r == 1;
}
public static bool Delete(CardDTO model)
{
 string sql = "delete from `card` where CID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.CID));
return r == 1;
}
public static bool Update(CardDTO model)
{
string sql = "update card set `AID`=@AID,`Type`=@Type,`Title`=@Title,`Money`=@Money,`Description`=@Description,`CardNumber`=@CardNumber,`OID`=@OID,`Credit_Line`=@Credit_Line,`Bill_Date`=@Bill_Date,`Due_Date`=@Due_Date where CID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("AID",model.AID),GetParams("Type",model.Type),GetParams("Title",model.Title),GetParams("Money",model.Money),GetParams("Description",model.Description),GetParams("CardNumber",model.CardNumber),GetParams("OID",model.OID),GetParams("Credit_Line",model.Credit_Line),GetParams("Bill_Date",model.Bill_Date),GetParams("Due_Date",model.Due_Date),GetParams("id",model.CID));
return r == 1;
}
public static bool Insert(CategoryDTO model)
{
 string sql = "insert into `category` (`ID`,`Title`,`ParentID`,`Purpose`,`OwnerID`) values (@ID,@Title,@ParentID,@Purpose,@OwnerID)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("Title",model.Title),GetParams("ParentID",model.ParentID),GetParams("Purpose",model.Purpose),GetParams("OwnerID",model.OwnerID));
model.ID=GetLastInsertID();
return r == 1;
}
public static bool Delete(CategoryDTO model)
{
 string sql = "delete from `category` where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.ID));
return r == 1;
}
public static bool Update(CategoryDTO model)
{
string sql = "update category set `Title`=@Title,`ParentID`=@ParentID,`Purpose`=@Purpose,`OwnerID`=@OwnerID where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("Title",model.Title),GetParams("ParentID",model.ParentID),GetParams("Purpose",model.Purpose),GetParams("OwnerID",model.OwnerID),GetParams("id",model.ID));
return r == 1;
}
public static bool Insert(Category_removeDTO model)
{
 string sql = "insert into `category_remove` (`ID`,`OwnerID`,`RemoveID`) values (@ID,@OwnerID,@RemoveID)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("OwnerID",model.OwnerID),GetParams("RemoveID",model.RemoveID));
model.ID=GetLastInsertID();
return r == 1;
}
public static bool Delete(Category_removeDTO model)
{
 string sql = "delete from `category_remove` where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.ID));
return r == 1;
}
public static bool Update(Category_removeDTO model)
{
string sql = "update category_remove set `OwnerID`=@OwnerID,`RemoveID`=@RemoveID where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("OwnerID",model.OwnerID),GetParams("RemoveID",model.RemoveID),GetParams("id",model.ID));
return r == 1;
}
public static bool Insert(Const_rateDTO model)
{
 string sql = "insert into `const_rate` (`ID`,`Rate`,`Months`,`Title`) values (@ID,@Rate,@Months,@Title)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("Rate",model.Rate),GetParams("Months",model.Months),GetParams("Title",model.Title));
model.ID=GetLastInsertID();
return r == 1;
}
public static bool Delete(Const_rateDTO model)
{
 string sql = "delete from `const_rate` where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.ID));
return r == 1;
}
public static bool Update(Const_rateDTO model)
{
string sql = "update const_rate set `Rate`=@Rate,`Months`=@Months,`Title`=@Title where ID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("Rate",model.Rate),GetParams("Months",model.Months),GetParams("Title",model.Title),GetParams("id",model.ID));
return r == 1;
}
public static bool Insert(OrganizationDTO model)
{
 string sql = "insert into `organization` (`OID`,`Title`,`URL`,`Order`,`Type`) values (@OID,@Title,@URL,@Order,@Type)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("Title",model.Title),GetParams("URL",model.URL),GetParams("Order",model.Order),GetParams("Type",model.Type));
model.OID=GetLastInsertID();
return r == 1;
}
public static bool Delete(OrganizationDTO model)
{
 string sql = "delete from `organization` where OID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.OID));
return r == 1;
}
public static bool Update(OrganizationDTO model)
{
string sql = "update organization set `Title`=@Title,`URL`=@URL,`Order`=@Order,`Type`=@Type where OID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("Title",model.Title),GetParams("URL",model.URL),GetParams("Order",model.Order),GetParams("Type",model.Type),GetParams("id",model.OID));
return r == 1;
}
public static bool Insert(RecordDTO model)
{
 string sql = "insert into `record` (`RID`,`AID`,`Money`,`Create_Time`,`Card_ID`,`Category_ID`,`Remark`) values (@RID,@AID,@Money,@Create_Time,@Card_ID,@Category_ID,@Remark)";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("AID",model.AID),GetParams("Money",model.Money),GetParams("Create_Time",model.Create_Time),GetParams("Card_ID",model.Card_ID),GetParams("Category_ID",model.Category_ID),GetParams("Remark",model.Remark));
model.RID=GetLastInsertID();
return r == 1;
}
public static bool Delete(RecordDTO model)
{
 string sql = "delete from `record` where RID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("id",model.RID));
return r == 1;
}
public static bool Update(RecordDTO model)
{
string sql = "update record set `AID`=@AID,`Money`=@Money,`Create_Time`=@Create_Time,`Card_ID`=@Card_ID,`Category_ID`=@Category_ID,`Remark`=@Remark where RID=@id";
var r = MySQLHelper.ExecuteNonQuery(sql,GetParams("AID",model.AID),GetParams("Money",model.Money),GetParams("Create_Time",model.Create_Time),GetParams("Card_ID",model.Card_ID),GetParams("Category_ID",model.Category_ID),GetParams("Remark",model.Remark),GetParams("id",model.RID));
return r == 1;
}
/// <summary>
/// 生成参数
/// </summary>
private static KeyValuePair<string, object> GetParams(string key, object value){return new KeyValuePair<string, object>(key, value);}
/// <summary>
/// 返回最后插入的ID
/// </summary>
private static int GetLastInsertID(){return int.Parse(MySQLHelper.ExecuteScalar("SELECT LAST_INSERT_ID()"));}

    }
}